/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package IFSv2.Forms.Utilities;

import IFSv2.Connection.DBConnection;
import IFSv2.Util.utilData;
import java.awt.event.ActionEvent;
import java.io.File;
import java.io.IOException;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JFileChooser;
import javax.swing.JOptionPane;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

/**
 *
 * @author Fulbert
 */
public class frmUploadExcel extends javax.swing.JPanel {

    private javax.swing.table.DefaultTableModel table;
    List<String> dataTypeList = new ArrayList<>();
    List<String> columnNameList = new ArrayList<>();

    /**
     * Creates new form frmUploadExcel
     */
    public frmUploadExcel() {
        initComponents();
        loadTables();
        loadButton.addActionListener(new loadBtnAction());
    }

    private void loadTables(){
        try {
            DatabaseMetaData meta = DBConnection.getActiveConnection().getMetaData();
            ResultSet databaseList = meta.getTables(null, null, "%", null);
            while(databaseList.next()){
                tableCombo.addItem(databaseList.getString("TABLE_NAME"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(frmUploadExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void loadExcel(File xlsFile){
        try {
            Workbook workbook = Workbook.getWorkbook(xlsFile);
            Sheet sheet = workbook.getSheet(0);
            
            table = (javax.swing.table.DefaultTableModel) jTable1.getModel();
             
            for(int i=0; i< sheet.getColumns() ; i++){
                table.addColumn(sheet.getCell(i, 0).getContents());
            }
            Object [] data = new Object[sheet.getColumns()];
            for(int i=1; i< sheet.getRows() ; i++){
                for(int j=0; j< sheet.getColumns(); j++){
                    data[j] = sheet.getCell(j,i).getContents();
                }
                table.addRow(data);
                
            }
        } catch (IOException | BiffException ex) {
            Logger.getLogger(frmUploadExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    private void openFile(){
    
        JFileChooser fileChooser = new JFileChooser();
        fileChooser.setDialogTitle("Load Excel File");
        fileChooser.setFileSelectionMode(JFileChooser.FILES_ONLY);
        fileChooser.setFileFilter(new javax.swing.filechooser.FileNameExtensionFilter("Excel File", "xls"));
        int retVal = fileChooser.showOpenDialog(this);
        if(retVal == JFileChooser.APPROVE_OPTION){
            loadExcel(fileChooser.getSelectedFile());
            filePathTbx.setText(fileChooser.getName(fileChooser.getSelectedFile()));
        }
    }

    private void getTableInfo(){
    
        String _sql = " SELECT " + table.getColumnName(0);
        
        for(int i=0; i<table.getColumnCount(); i++){
            _sql = _sql + " , " + table.getColumnName(i);
        }
        
        _sql = _sql + " FROM " + tableCombo.getSelectedItem();
        
        try {
            ResultSet rs = DBConnection.getActiveConnection().createStatement().executeQuery(_sql);
            ResultSetMetaData tblType = rs.getMetaData();
            int numOfCol = tblType.getColumnCount();
            for(int i=1; i<=numOfCol; i++){
                dataTypeList.add(tblType.getColumnTypeName(i));
                columnNameList.add(tblType.getColumnName(i));
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(frmUploadExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
        
    }

    public String getDataTypeName(String column){
        int i = columnNameList.indexOf(column);
        return dataTypeList.get(i);
    }

    public void uploadToDatabase(){
        
        getTableInfo();
        
        for(int row=0; row < table.getRowCount(); row++){

            try{
            
            String _sql = " INSERT INTO " + tableCombo.getSelectedItem().toString() + " ( " + table.getColumnName(0) ;
            //loop for table columns
            for(int col=1; col<table.getColumnCount(); col++){
                _sql = _sql + " , " + table.getColumnName(col);
            }
            
            _sql = _sql + " ) VALUES ( " + utilData.FormatData(table.getValueAt(row, 0).toString(), getDataTypeName(table.getColumnName(0)));

            for(int colVal = 1; colVal<table.getColumnCount(); colVal++){
                _sql = _sql + " , " + utilData.FormatData(table.getValueAt(row, colVal).toString(), getDataTypeName(table.getColumnName(colVal)));
            }
            
            _sql = _sql + ")";
            
            DBConnection.getActiveConnection().createStatement().executeUpdate(_sql);
            
            }catch(Exception e){
                Logger.getLogger(frmUploadExcel.class.getName()).log(Level.SEVERE, null, e);
                JOptionPane.showMessageDialog(this, e.toString().substring(e.toString().lastIndexOf(':') + 1, e.toString().length()) + "\n Please Contact TIM Support", e.toString().substring(0, e.toString().lastIndexOf(':')-1), JOptionPane.ERROR_MESSAGE);
                try {
                    DBConnection.getActiveConnection().rollback();
                } catch (SQLException ex) {
                    Logger.getLogger(frmUploadExcel.class.getName()).log(Level.SEVERE, null, ex);
                }
                break;
            }  
            progressBar.setValue(row);
            progressBar.repaint();
        }
        try {
            DBConnection.getActiveConnection().commit();
        } catch (SQLException ex) {
            Logger.getLogger(frmUploadExcel.class.getName()).log(Level.SEVERE, null, ex);
        }
        JOptionPane.showMessageDialog(this, "Data loaded!", "Upload Excel", JOptionPane.INFORMATION_MESSAGE);
        loadButton.setEnabled(true);
    }

    /**
     * This method is called from within the constructor to initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is always
     * regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        filePathTbx = new javax.swing.JTextField();
        openFileButton = new javax.swing.JButton();
        jScrollPane1 = new javax.swing.JScrollPane();
        jTable1 = new javax.swing.JTable();
        loadButton = new javax.swing.JButton();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        tableCombo = new javax.swing.JComboBox();
        progressBar = new javax.swing.JProgressBar();

        setBackground(new java.awt.Color(255, 255, 255));

        openFileButton.setText("...");
        openFileButton.addMouseListener(new java.awt.event.MouseAdapter() {
            public void mouseClicked(java.awt.event.MouseEvent evt) {
                openFileButtonMouseClicked(evt);
            }
        });

        jTable1.setModel(new javax.swing.table.DefaultTableModel(
            new Object [][] {

            },
            new String [] {

            }
        ));
        jTable1.setAutoResizeMode(javax.swing.JTable.AUTO_RESIZE_OFF);
        jTable1.getTableHeader().setReorderingAllowed(false);
        jScrollPane1.setViewportView(jTable1);

        loadButton.setText("Upload");

        jLabel1.setText("Load From");

        jLabel2.setText("Load To");

        progressBar.setForeground(new java.awt.Color(153, 153, 255));

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(this);
        this.setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 380, Short.MAX_VALUE)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addComponent(jLabel1)
                            .addComponent(jLabel2))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                            .addComponent(filePathTbx)
                            .addComponent(tableCombo, 0, 188, Short.MAX_VALUE))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                            .addGroup(layout.createSequentialGroup()
                                .addComponent(progressBar, javax.swing.GroupLayout.PREFERRED_SIZE, 0, Short.MAX_VALUE)
                                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                                .addComponent(loadButton))
                            .addComponent(openFileButton))))
                .addContainerGap())
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                    .addComponent(filePathTbx, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                    .addComponent(openFileButton)
                    .addComponent(jLabel1))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                        .addComponent(jLabel2)
                        .addComponent(tableCombo, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addComponent(loadButton))
                    .addComponent(progressBar, javax.swing.GroupLayout.PREFERRED_SIZE, 23, javax.swing.GroupLayout.PREFERRED_SIZE))
                .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.RELATED)
                .addComponent(jScrollPane1, javax.swing.GroupLayout.DEFAULT_SIZE, 220, Short.MAX_VALUE)
                .addContainerGap())
        );
    }// </editor-fold>//GEN-END:initComponents
//uploadToDatabase();
    private void openFileButtonMouseClicked(java.awt.event.MouseEvent evt) {//GEN-FIRST:event_openFileButtonMouseClicked
        try {
            table.setRowCount(0);
            table.setColumnCount(0);
        } catch (Exception e) {
        }
        openFile();
    }//GEN-LAST:event_openFileButtonMouseClicked

    public class loadBtnAction implements java.awt.event.ActionListener{

        @Override
        public void actionPerformed(ActionEvent e) {
            progressBar.setMaximum(table.getRowCount());
            progressBar.setStringPainted(true);
            progressBar.setValue(0);
            loadButton.setEnabled(false);
            new Thread(new ThreadRun()).start();
        }
    }

    public class ThreadRun implements Runnable{

        @Override
        public void run() {
            try {
                Thread.sleep(10);
            } catch (InterruptedException ex) {
                Logger.getLogger(frmUploadExcel.class.getName()).log(Level.SEVERE, null, ex);
            }
            uploadToDatabase();
        }
        
    
    }

    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JTextField filePathTbx;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JTable jTable1;
    private javax.swing.JButton loadButton;
    private javax.swing.JButton openFileButton;
    private javax.swing.JProgressBar progressBar;
    private javax.swing.JComboBox tableCombo;
    // End of variables declaration//GEN-END:variables
}
